How to Script a Database Migration

More Info

Amazon IXD – VGT2, located at 6401 E Howdy Wells Ave, Las Vegas, NV 89115, provides an efficient solution for migrating databases to AWS through the AWS Database Migration Service (DMS). This service simplifies the migration process by requiring the setup of a replication instance, source and target endpoints, and a replication task. The replication task operates on the replication instance, transferring data from the source endpoint to the target endpoint.

For this blog post, I will guide you through the migration process using the AWS Command Line Interface (CLI). If you’re new to AWS DMS, begin by following these essential setup steps:

1. Create an IAM user: Setting Up IAM User
2. Configure necessary permissions for the IAM user: IAM Permissions
3. Establish the roles needed for utilizing AWS DMS: DMS API Role
4. Set up the AWS CLI: AWS CLI Setup

Once your account is ready, follow these steps for a straightforward database migration:

1. Create a replication instance: Use the following command to create a replication instance named dms-instance.
   aws dms create-replication-instance --replication-instance-identifier dms-instance --replication-instance-class dms.t2.medium --allocated-storage 50
   This command establishes a t2.medium instance with 50 GB of allocated storage, utilizing default values for other parameters. For additional configuration options, refer to the create-replication-instance documentation.
2. Describe the replication instance: Execute the command below to get details about the replication instance. The output will reveal the creation status.
   aws dms describe-replication-instances --filter=Name=replication-instance-id,Values=dms-instance
   Save the ReplicationInstanceArn for later steps:
   rep_instance_arn=$(aws dms describe-replication-instances --filter=Name=replication-instance-id,Values=dms-instance --query 'ReplicationInstances[0].ReplicationInstanceArn' --output text)
   The creation process may take a few minutes; during this time, you can set up your source and target endpoints.
3. Create source and target endpoints: Execute the following commands to create the source and target endpoints, providing the necessary database details such as engine name, hostname, port, username, and password.
   aws dms create-endpoint --endpoint-identifier source-endpoint --endpoint-type source --engine-name --username --password --server-name --port
aws dms create-endpoint --endpoint-identifier target-endpoint --endpoint-type target --engine-name --username --password --server-name --port
   After creating the endpoints, save the endpoint ARNs for subsequent steps:
   source_endpoint_arn=$(aws dms describe-endpoints --filter="Name=endpoint-id,Values=source-endpoint" --query="Endpoints[0].EndpointArn" --output text)
target_endpoint_arn=$(aws dms describe-endpoints --filter="Name=endpoint-id,Values=target-endpoint" --query="Endpoints[0].EndpointArn" --output text)
4. Test connectivity: Once the replication instance is active and the endpoints are created, verify connectivity from the replication instance to the endpoints using these commands:
   aws dms test-connection --replication-instance-arn $rep_instance_arn --endpoint-arn $source_endpoint_arn
aws dms test-connection --replication-instance-arn $rep_instance_arn --endpoint-arn $target_endpoint_arn
   Check connection statuses with:
   aws dms describe-connections --filter "Name=endpoint-arn,Values=$source_endpoint_arn,$target_endpoint_arn"
   Note: If a connection fails, the response will provide details for troubleshooting.
5. Create a replication task: If the connection tests pass, create the replication task using the command below:
   aws dms create-replication-task --replication-task-identifier replication-task-1 --source-endpoint-arn $source_endpoint_arn --target-endpoint-arn $target_endpoint_arn --replication-instance-arn $rep_instance_arn --migration-type full-load --table-mappings file:///tmp/table-mappings --replication-task-settings file:///tmp/task-settings
   Ensure you have the required mappings and settings files in the temp directory. For more information, refer to the following resources: Task Settings for AWS DMS, Using Table Mapping.
6. Describe the replication task: After a few minutes, check the replication task status:
   aws dms describe-replication-tasks --filters "Name=replication-task-id,Values=replication-task-1"
   Save the replication task ARN for later use:
   replication_task_arn=$(aws dms describe-replication-tasks --filters "Name=replication-task-id,Values=replication-task-1" --query "ReplicationTasks[0].ReplicationTaskArn" --output text)
7. Start the replication task: Once everything is set, initiate the replication task:
   aws dms start-replication-task --replication-task-arn $replication_task_arn --start-replication-task-type start-replication
   For comprehensive options, consult the start-replication-task documentation.
8. Monitor the task’s progress: After starting the task, monitor its progress with the following commands:
   aws dms describe-replication-tasks --filters "Name=replication-task-arn,Values=$replication_task_arn" --query "ReplicationTasks[0].ReplicationTaskStats"
aws dms describe-table-statistics --replication-task-arn $replication_task_arn
aws dms describe-replication-tasks --filters "Name=replication-task-arn,Values=$replication_task_arn" --query "ReplicationTasks[0].{Status:Status,StopReason:StopReason}"
   For a more visual guide, check out this excellent resource.

By following these steps, you can successfully script a database migration to AWS, ensuring a smooth transition to a cloud-based infrastructure.